One way to improve the speed at which transactions are retrieved from a database is to create and use indexes. You can implement indexes on the database side where the queries are executed.
You can use indexes any time a back-end database is used, such as for archive/retrieval and for use with Library manager and Documanage.
The Documanage font-end interface does not allow for indexing, but you can do it on the back-end side (SQL Server). You can also do the indexing manually on the back-end after the tables have been created. Set the index on the search key fields of any relational table, such as Key1, Key2, KeyID, for faster searches.
By using indexes, the database engine avoids having to do a full table scan when searching for data. A table can have any number of indexes, but keep in mind that when you insert or delete rows, you must update all indexes on the table. When a column is updated, all indexes on the column must be updated.
Since both Library manager and archive tables are constantly updated and since there is a trade-off between the retrieval speed for queries on a table and time it takes to update the table, you may want to limit the indexes to the Key1, Key1, KeyID fields for the Archive index table. For Library manager, consider only using the FileName, FileType, and Effectiv fields.
You can also fine tune database performance using clustering. The cluster key is the column or group of columns the clustered tables have in common. You specify the columns of the cluster key when creating the cluster. Each cluster key value is stored once in the cluster and in the cluster index, no matter how many rows in different tables contain the value. When you have two tables that share the same columns, such as the APPIDX and CARARC tables which share the ArcKey field, you can use a cluster on those two tables.
This lets the database store all rows for each ArcKey from both the APPIDX and CARARC tables in the same data blocks. Do not use clusters for tables that are accessed individually. The benefit is reduced disk I/O and improved access time when doing a retrieval. Although clustering occurs when the table is created—a point at which you have no control— you may be able to cluster the tables after creation time using an Alter Table statement.
You can run an Explain Plan statement on the Oracle side to see how the query mechanism changes based on associated indexes and clusters. The retrieval time difference is usually very noticeable when using tables with lots of rows.
SQL Server has a GUI interface you can use to manage table relationships, keys, and indexes. You can access it from the table design view (by clicking the Table Properties button on toolbar).
If the archive is a flat file and the index is in xBase, limit your search keys to one to retrieve from a large index and archive. Performance slows when you use two or more search keys.
If you need two or more search keys, consider using a relational DBMS such as SQL, DB2, or Oracle to store the archive and index.
© Copyright 2016, Oracle and/or its affiliates. All rights reserved. Legal notices.